-- OpenCloudOS 节点管理系统数据库Schema
-- 创建数据库（如果需要）
-- CREATE DATABASE node_management;

-- 使用数据库
-- \c node_management;

-- Agent节点信息表
CREATE TABLE IF NOT EXISTS agents (
    id SERIAL PRIMARY KEY,
    agent_id VARCHAR(255) UNIQUE NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    groups TEXT[], -- PostgreSQL数组类型存储分组信息
    token_hash VARCHAR(255) NOT NULL, -- 存储Token的哈希值，不存储明文
    os_info TEXT,
    status VARCHAR(50) DEFAULT 'offline', -- online, offline
    last_heartbeat TIMESTAMP WITH TIME ZONE,
    registered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 任务记录表
CREATE TABLE IF NOT EXISTS tasks (
    id SERIAL PRIMARY KEY,
    command_id VARCHAR(255) UNIQUE NOT NULL,
    command_type VARCHAR(100) NOT NULL,
    payload JSONB, -- 使用JSONB存储任务参数
    target_group VARCHAR(255),
    status VARCHAR(50) DEFAULT 'pending', -- pending, completed, failed
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE
);

-- 任务执行结果表
CREATE TABLE IF NOT EXISTS task_results (
    id SERIAL PRIMARY KEY,
    command_id VARCHAR(255) NOT NULL,
    agent_id VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL, -- success, error
    result JSONB, -- 存储执行结果
    completed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE
);

-- 心跳记录表（可选，用于调试和监控）
CREATE TABLE IF NOT EXISTS heartbeats (
    id SERIAL PRIMARY KEY,
    agent_id VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE
);

-- 创建索引提高查询性能
CREATE INDEX IF NOT EXISTS idx_agents_agent_id ON agents(agent_id);
CREATE INDEX IF NOT EXISTS idx_agents_status ON agents(status);
CREATE INDEX IF NOT EXISTS idx_agents_last_heartbeat ON agents(last_heartbeat);
CREATE INDEX IF NOT EXISTS idx_tasks_command_id ON tasks(command_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_task_results_command_id ON task_results(command_id);
CREATE INDEX IF NOT EXISTS idx_task_results_agent_id ON task_results(agent_id);
CREATE INDEX IF NOT EXISTS idx_heartbeats_agent_id ON heartbeats(agent_id);
CREATE INDEX IF NOT EXISTS idx_heartbeats_timestamp ON heartbeats(timestamp);

-- 创建更新时间戳的函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建触发器，自动更新updated_at字段
CREATE TRIGGER update_agents_updated_at BEFORE UPDATE ON agents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
